package com.bunde.repo;

import com.bunde.repo.model.InventoryModel;
import com.sun.org.apache.bcel.internal.generic.FSUB;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.*;

public class InventoryExcelManager {

    Workbook wb = null;

    // 检验文件类型, 默认xls
    public ExcelType excelType = ExcelType.XLS;

    // 从excel中加载数据
    public Map<String, List<List<Object>>> loadExcel(File file) throws Exception {
        // 检查文件类型
        checkFileType(file);
        // 创建excel文件实例
        wb = WorkbookFactory.create(file);

        Map<String, List<List<Object>>> res = new HashMap<>();
        List<List<Object>> data = new ArrayList<>();
        // 假定文件中只有一个文件中excel表
        Iterator<Sheet> sheetIterator = wb.sheetIterator();
        if(!sheetIterator.hasNext()) return res;
        Sheet sheet = sheetIterator.next();

        // 读取表头数据
        List<Object> header = new ArrayList<>();
        Iterator<Row> rowIterator = sheet.rowIterator();
        if (!rowIterator.hasNext()){
            throw new Exception("excel 文件格式错误，未检测到表头");
        }
        Row tRow = rowIterator.next();
        Iterator<Cell> cellIterator = tRow.cellIterator();
        for(; cellIterator.hasNext(); ){
            Cell cell = cellIterator.next();
            header.add(cell.getStringCellValue());
        }
        data.add(header);

        // 预留行数：5
        for(int i = 1; i <= 5; i++){
            data.add(new ArrayList<>());
        }

        // 处理表格中的第二行的统计行
        if (!rowIterator.hasNext()){
            return res;
        }
        tRow = rowIterator.next();
        List<Object> tRowData = data.get(1);
        for(cellIterator = tRow.cellIterator(); cellIterator.hasNext(); ){
            tRowData.add(getCellValue(cellIterator.next()));
        }

        // 添加数据主体行
        while(rowIterator.hasNext()) {
            tRow = rowIterator.next();
            List<Object> list = new ArrayList<>();
            for (cellIterator = tRow.cellIterator(); cellIterator.hasNext(); ) {
                Cell cell = cellIterator.next();
//                System.out.println("cell = " + getCellValue(cell));
                list.add(getCellValue(cell));
            }
            data.add(list);
        }
        res.put(sheet.getSheetName(), data);
        return res;
    }

    public Map<String, List<List<Object>>> loadExcel(String filename) throws Exception {
        return loadExcel(new File(filename));
    }


    // 往excel中写入数据
    public void writeToExcel(List<List<Object>> data, File file, int startWeek, int endWeek) throws Exception {
        checkFileType(file);
        if (this.excelType == ExcelType.XLS){
            wb = new HSSFWorkbook();
        }else{
            wb = new XSSFWorkbook();
        }
        Sheet sheet = wb.createSheet();

        String header[] = {"渠道", "国家", "负库存"};
        // 写入表头，内容固定，固定列+周数
        Row row = sheet.createRow(0);
        int wkCol = data.get(0).size();
        for(int i = 0; i < 3; i++){
            row.createCell(i).setCellValue(header[i]);
        }
        for(int i = startWeek; i <= endWeek; i++){
            row.createCell(i - startWeek + 3).setCellValue("WK" + i);
        }

        // 第二行
        // 合并前3列，汇总各个周的总销售库存
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 2));
        Row row1 = sheet.createRow(1);
        row1.createCell(0).setCellValue("发货前库存");
        for (int i = startWeek; i <= endWeek; i++){
            row1.createCell(i - startWeek + 3).setCellValue(123);
        }

        // 写入具体数据
        int dataStartRow = 2;
        for(int i = 0; i < data.size(); i++){
            Row row2 = sheet.createRow(dataStartRow++);
            InventoryModel inventoryModel = (InventoryModel) (data.get(i).get(0));
            // 销售点
            row2.createCell(0).setCellValue(inventoryModel.getSiteName());
            row2.createCell(1).setCellValue(inventoryModel.getSiteCountry());
            row2.createCell(2).setCellValue(-1);
            List<Object> rowData = data.get(i);
            for (int j = 1; j < rowData.size(); j++) {
                Object o = rowData.get(j);
                //
                if(o instanceof InventoryModel){
                    row2.createCell(2 + j).setCellValue(((InventoryModel) o).getNum());
                }else {
                    row2.createCell(2 + j).setCellValue((Integer)o);
                }
            }
        }

        // 写入文件
        try(OutputStream outputStream = new FileOutputStream(file)){
            wb.write(outputStream);
            wb.close();
        }catch (IOException e){
            System.out.println("error");
            e.printStackTrace();
        }
    }

    public void writeToExcel(List<List<Object>> data, String filename,int startWeek, int endWeek) throws Exception {
        File file = new File(filename);
        if(!file.exists()){
            file.createNewFile();
        }
        writeToExcel(data, file, startWeek, endWeek);
    }

    // 单元格的数据类型
    public Object getCellValue(Cell cell){
        Object res = null;
        if(cell.getCellTypeEnum() == CellType.BOOLEAN){
            res = cell.getBooleanCellValue();
        }else if(cell.getCellTypeEnum() == CellType.NUMERIC){
            res = cell.getNumericCellValue();
        }else if(cell.getCellTypeEnum() == CellType.STRING){
            res = cell.getStringCellValue();
        }else if(cell.getCellTypeEnum() == CellType.ERROR){
            res = cell.getErrorCellValue();
        }else{
            res = new String();
        }
        return res;
    }

    // 检测文件类型
    public void checkFileType(File file) throws Exception {
        String name = file.getName();
        String suffix = name.substring(name.indexOf(".") + 1);
        if(suffix.equals("xls")){
            this.excelType = ExcelType.XLS;
        }else if(suffix.equals("xlsx")){
            this.excelType = ExcelType.XLSX;
        }else{
            throw new Exception("不合法的文件格式");
        }
    }
}
